package bancosys.tec.utils.db.converter;

import java.io.File;
import java.io.FileNotFoundException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Marcio Ribeiro (mmr)
 * @created Apr 25, 2007
 */
public class OracleToDB2Converter extends StatementDbConverter {

    private static final int MAX_DB2_CONSTRAINT_LEN = 18;

    private static final int MAX_SMALL_INT_FIELD_SIZE = 4;

    private static final int MAX_INT_FIELD_SIZE = 9;

    private Pattern numberFieldPattern = Pattern.compile("(\\w+)\\s+NUMBER\\s*\\(\\s*(\\d+)\\s*\\)");

    private Pattern pkPattern =
            Pattern.compile("ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+CONSTRAINT\\s+\\w+\\s+PRIMARY\\s+KEY\\s*\\(\\s*(\\w+)\\s*\\)");

    private Pattern constraintPattern = Pattern.compile("ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+CONSTRAINT\\s+(\\w+)\\s+");

    private Map<String, Integer> numberFields = new HashMap<String, Integer>();

    private Map<String, Integer> ultimoIndiceParaTabela = new HashMap<String, Integer>();

    /**
     * Construtor.
     * 
     * @param inputFile Arquivo do schema.
     * @param outputFile Arquivo do schema convertido.
     * @param dbName nome do banco de daods.
     * @throws FileNotFoundException falha ao acessar o arquivo.
     * @throws MalformedURLException falha na formação do nome do arquivo.
     */
    public OracleToDB2Converter(File inputFile, File outputFile, String dbName) throws FileNotFoundException, MalformedURLException {
        super(inputFile, outputFile, dbName);
    }

    /**
     * Construtor.
     * 
     * @param inputFile Arquivo do schema.
     * @param outputFile Arquivo do schema convertido.
     * @param dbName nome do banco de daods.
     */
    public OracleToDB2Converter(URL inputFile, File outputFile, String dbName) {
        super(inputFile, outputFile, dbName);
    }

    /**
     * Retorna o header do arquivo convertido. <br>
     * Null para o db2.
     * 
     * @return Header null para o db2.
     */
    @Override
    protected String getHeader() {
        return "";
    }

    /**
     * Converte statements oracle para db2.
     * 
     * @return o statement convertido.
     * @param s o statement
     */
    @Override
    protected String convertStatement(String s) {
        String stm = s;
        // Ignore
        if (s.startsWith("COMMENT")) {
            return null;
        }

        String startWith = "100000";
        Matcher fieldMatcher = this.numberFieldPattern.matcher(stm);
        while (fieldMatcher.find()) {
            String coluna = fieldMatcher.group(1);
            int tamanho = Integer.parseInt(fieldMatcher.group(2));

            this.numberFields.put(coluna, tamanho);

            if (tamanho > MAX_INT_FIELD_SIZE) {
                stm = stm.replaceAll(coluna + "\\s+NUMBER\\s*\\(\\s*\\d+\\s*\\)", coluna + " BIGINT");
            } else if (tamanho > MAX_SMALL_INT_FIELD_SIZE) {
                stm = stm.replaceAll(coluna + "\\s+NUMBER\\s*\\(\\s*\\d+\\s*\\)", coluna + " INTEGER");
            } else {
                stm = stm.replaceAll(coluna + "\\s+NUMBER\\s*\\(\\s*\\d+\\s*\\)", coluna + " SMALLINT");
            }
        }

        Matcher pkMatcher = this.pkPattern.matcher(s);
        if (pkMatcher.find()) {
            String coluna = pkMatcher.group(2);
            if (this.numberFields.containsKey(coluna)) {
                int tamanho = this.numberFields.get(coluna);
                if (tamanho <= MAX_SMALL_INT_FIELD_SIZE) {
                    startWith = "10000";
                }
                stm =
                        stm.replaceAll("ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+CONSTRAINT\\s+\\w+\\s+PRIMARY\\s+KEY\\s*\\(\\s*(\\w+)\\s*\\)", s
                                + this.getNewStatementDelimiterChar()
                                + "\nALTER TABLE $1 ALTER COLUMN $2 SET GENERATED BY DEFAULT AS IDENTITY ( START WITH " + startWith
                                + " )\n");
            }
        }

        /*
         * Trata nome de constraints com mais de 18 caracteres. O DB2 v9.0 aceita apenas nomes com no máximo 18 caracteres.
         */
        Matcher constraintMatcher = this.constraintPattern.matcher(stm);
        if (constraintMatcher.find()) {
            String tabela = constraintMatcher.group(1);
            String constraint = constraintMatcher.group(2);
            if (constraint.length() > MAX_DB2_CONSTRAINT_LEN) {
                String novaConstraint = this.cortaNome(tabela, constraint);
                stm = stm.replaceAll("CONSTRAINT\\s+(\\w+)\\s+", "CONSTRAINT " + novaConstraint + " ");
            }
        }

        stm = stm.replaceAll("NOT\\s+NULL", "NOT_NULL");
        stm = stm.replaceAll("\\s+NULL", "");
        stm = stm.replaceAll("NOT_NULL", "NOT NULL");

        // DB2
        stm = stm.replaceAll("NUMBER\\((\\d+,\\d+)\\)", "DECIMAL($1)");
        stm = stm.replaceAll("NVARCHAR2", "VARCHAR");
        stm = stm.replaceAll("VARCHAR2", "VARCHAR");
        stm = stm.replaceAll("DATE", "TIMESTAMP");

        return stm;
    }

    /**
     * Retorna um nome acrescido de um número indexador. O nome terá no máximo 18 caracteres.
     * 
     * @param tabela Nome da tabela que contem o "alter table" ou "create index".
     * @param constraint O nome da constraint que será contada.
     * @return O nome da constraint cortada e acrescida de um indice.
     */
    private String cortaNome(String tabela, String constraint) {
        if (!this.ultimoIndiceParaTabela.containsKey(tabela)) {
            this.ultimoIndiceParaTabela.put(tabela, 0);
        }

        Integer ultimo = this.ultimoIndiceParaTabela.get(tabela);
        Integer prox = ultimo + 1;
        this.ultimoIndiceParaTabela.put(tabela, prox);

        String novaConstraint = constraint.substring(0, MAX_DB2_CONSTRAINT_LEN - prox.toString().length()) + prox.toString();

        return novaConstraint;
    }

    /**
     * Se a linha tem delimitador.
     * 
     * @param line a linha.
     * @return true se a linha tem delimitador.
     */
    @Override
    protected boolean gotDelimiter(String line) {
        return line.matches("^" + this.getOldStatementDelimiterChar() + "$");
    }

    /**
     * O delimitador a ser substituido.
     * 
     * @return o delimitador antigo.
     */
    @Override
    protected char getOldStatementDelimiterChar() {
        return '/';
    }

    /**
     * O novo delimitador.
     * 
     * @return o delimitador novo.
     */
    @Override
    protected char getNewStatementDelimiterChar() {
        return ';';
    }
}